package b1w1nn1ng.crossf1t.db;

import java.sql.*;
import java.sql.Date;
import java.text.ParseException;
import java.util.*;

import b1w1nn1ng.crossf1t.business.*;

public class Database {

	private static String database = "b1wdb";
	private static String username = "cs110sbd";
	private static String password = "cs110s_b1w";

	/**
	 * @param userName
	 * @return Returns
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static String getQuestionByUsername(String userName)
	throws ClassNotFoundException, SQLException {

		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT \"user\".security_question FROM public.\"user\" WHERE \"user\".username = ?;");
		stmt.setString(1, userName);
		// return the security question associated with that username's account
		try {
			ResultSet rs = stmt.executeQuery();
			rs.next();
			String result = rs.getString(1);

			// Checks for null query
			if (!result.equals("")) {
				conn.close();
				return result;
			}
		} catch (SQLException e) {
			System.err.println(e);

			// if there was an error return null
			conn.close();
			return null;
		}
		// since result query was null return null
		conn.close();
		return null;
	}

	public static boolean updateFriendList(int id, ArrayList<Integer> friendList)
	throws SQLException, ClassNotFoundException {
		Integer[] friendArray = new Integer[friendList.size()];
		friendList.toArray(friendArray);

		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("UPDATE \"user\" SET friends=? WHERE \"user\".id = ?;");
		Array toInsert = conn.createArrayOf("integer", friendArray);
		stmt.setArray(1, toInsert);
		stmt.setInt(2, id);

		int result = stmt.executeUpdate();

		if (result == 0) {
			conn.close();
			return true;
		} else {
			conn.close();
			return false;
		}
	}

	/**
	 * @param id
	 * @return A User object containing the user info for a given id number, or
	 *         null if there is no such id
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static User getUserByID(int id) throws SQLException,
	ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT \"user\".email,\"user\".security_answer,\"user\".username,\"user\".security_question,\"user\".\"password\",\"user\".friend_code,\"user\".last_name,\"user\".first_name,\"user\".id, fitness_info.weight, fitness_info.height, \"user\".friends FROM public.\"user\", public.fitness_info WHERE \"user\".id = ? AND fitness_info.id = \"user\".id;");
		stmt.setInt(1, id);

		try {

			// executes query
			ResultSet rs = stmt.executeQuery();
			rs.next();

			// in first row of query result we
			// get the string located in the first column
			String result = rs.getString(1);

			// Checks for null query result
			if (!result.equals("")) {

				// store each column of the result as a local to send to the
				// user constructor
				int user_id = rs.getInt("id");
				String first_name = rs.getString("first_name");
				String last_name = rs.getString("last_name");
				int friend_code = rs.getInt("friend_code");
				String password = rs.getString("password");
				String security_question = rs.getString("security_question");
				String username = rs.getString("username");
				String security_answer = rs.getString("security_answer");
				String email = rs.getString("email");
				int weight = rs.getInt("weight");
				int height = rs.getInt("height");
				ArrayList<Integer> friendArrayList = null;
				Object[] tempArray = (Object[]) rs.getArray("friends").getArray();
				/*int[] friendList = new int[tempArray.length];
				for(int i = 0; i < tempArray.length; i++ ){
					friendList[i] = (Integer) tempArray[i];
				}*/

				friendArrayList = new ArrayList<Integer>();

				for (int i = 0; i < tempArray.length; i++) {
					friendArrayList.add((Integer)tempArray[i]);
				}
				
				ArrayList<String> goals = Database.getGoals(user_id);
				
				// use given locals to create new user object
				User user = new User(username, password, email, first_name,
						last_name, security_question, security_answer,
						friend_code, user_id, weight, height, friendArrayList, goals);
				conn.close();
				return user;
			}
		} catch (SQLException e) {
			System.err.println(e);
			conn.close();
			return null;
		}
		conn.close();
		return null;
	}

	/**
	 * @param username
	 * @return A User object containing all the user info for a given username,
	 *         or null if there is no such username
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static User getUserByUsername(String username) throws SQLException,
	ClassNotFoundException {
		User user = getUserByID(getIdByUsername(username));
		if (user != null) {
			return user;
		}

		return null;
	}

	/**
	 * @param username
	 * @return the corresponding id for a given username. Returns 0 and an eror
	 *         is username is invalid
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static int getIdByUsername(String user) throws SQLException,
	ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT \"user\".id FROM public.\"user\" WHERE \"user\".username = ?;");
		stmt.setString(1, user);
		// return the security question associated with that username's account
		try {
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int result = rs.getInt(1);

			// Checks for null query
			if (!(result == 0)) {
				conn.close();
				return result;
			}
		} catch (SQLException e) {
			System.err.print(e);
			System.out.println(": Username Invalid");

			// if there was an error return null
			conn.close();
			return 0;
		}
		// since result query was null return null
		conn.close();
		return 0;
	}

	/**
	 * @param user
	 * @return True is the user was successfully inserted, false otherwise
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static User addUser(User user) throws SQLException,
	ClassNotFoundException {
		int friendCode;
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT max(friend_code) FROM \"user\";");
		ResultSet rs = stmt.executeQuery();
		rs.next();
		friendCode = rs.getInt(1)+1;



		stmt = conn
		.prepareStatement("INSERT INTO \"user\"(first_name, last_name, \"password\", security_question, username, security_answer, email, friend_code) VALUES (?, ?, md5(?), ?, ?, ?, ?, ?);");
		// Inserts each parameter of a User object into the database
		stmt.setString(1, user.getFirstName());
		stmt.setString(2, user.getLastName());
		stmt.setString(3, user.getPassword());
		stmt.setString(4, user.getQuestion());
		stmt.setString(5, user.getUsername());
		stmt.setString(6, user.getAnswer());
		stmt.setString(7, user.getEmail());
		stmt.setInt(8, friendCode);

		stmt.execute();

		int user_id = getIdByUsername(user.getUsername());
		stmt = conn
		.prepareStatement("INSERT INTO fitness_info(id, weight, height) VALUES (?, ?, ?);");

		stmt.setInt(1, user_id);
		stmt.setInt(2, user.getWeight());
		stmt.setInt(3, user.getHeight());
		stmt.execute();
		
		stmt = conn
		.prepareStatement("INSERT INTO user_roles(user_ref, username, \"role\") VALUES (?, ?, ?);");
		stmt.setInt(1, user_id);
		stmt.setString(2, user.getUsername());
		stmt.setString(3, "user");
		
		stmt.execute();
		
		ArrayList<Integer> friendList = new ArrayList<Integer>();
		Database.updateFriendList(user_id, friendList);
		
		

		User newUser = getUserByUsername(user.getUsername());
		
		//adds weight to weight table
		stmt = conn
		.prepareStatement("INSERT INTO weight(id, date, weight) VALUES (?, ?, ?);");
		Calendar cal = Calendar.getInstance();
		java.util.Date date = new java.util.Date();
		cal.setTime(date);
		Date d = new Date(cal.getTimeInMillis());
		stmt.setDate(2, d);
		stmt.setInt(3, newUser.getWeight());
		stmt.setInt(1, newUser.getId());
		stmt.execute();
		
		conn.close();
		return newUser;


	}
	/**
	 * @param user
	 * @return True is the user was successfully inserted, false otherwise
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static User updateUser(User user) throws SQLException,
	ClassNotFoundException {
		//TODO THIS METHOD IS IN BETA
		//IT RESERVES THE RIGHT TO VOMIT IN YOUR FACE
		int friendCode;
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT max(friend_code) FROM \"user\";");
		ResultSet rs = stmt.executeQuery();
		rs.next();
		//friendCode = rs.getInt(1);
		friendCode = user.getFriendCode(); 



		stmt = conn
		.prepareStatement("UPDATE \"user\"SET first_name=?, last_name=?, security_question=?, username=?, security_answer=?, email=?, friend_code=? WHERE \"user\".id=?");
		// Inserts each parameter of a User object into the database
		stmt.setString(1, user.getFirstName());
		stmt.setString(2, user.getLastName());
		stmt.setString(3, user.getQuestion());
		stmt.setString(4, user.getUsername());
		stmt.setString(5, user.getAnswer());
		stmt.setString(6, user.getEmail());
		stmt.setInt(7, friendCode);
		stmt.setInt(8, user.getId());

		stmt.execute();
		
		//Check for update password
		if(!Database.getPasswordByUsername(user.getUsername()).equals(user.getPassword())){
			stmt = conn
				.prepareStatement("UPDATE \"user\"SET \"password\"=md5(?)WHERE \"user\".id=?");
			stmt.setString(1, user.getPassword());
			stmt.setInt(2, user.getId());
			stmt.execute();
		}
		
		
		
		stmt = conn
		.prepareStatement("SELECT fitness_info.weight FROM public.fitness_info WHERE fitness_info.id = ?;");
		stmt.setInt(1, user.getId());
		rs = stmt.executeQuery();
		rs.next();
		int origWeight = rs.getInt(1);
		if(origWeight!= user.getWeight()){
			stmt = conn
			.prepareStatement("INSERT INTO weight(id, date, weight) VALUES (?, ?, ?);");
			Calendar cal = Calendar.getInstance();
			java.util.Date date = new java.util.Date();
			cal.setTime(date);
			Date d = new Date(cal.getTimeInMillis());
			stmt.setDate(2, d);
			stmt.setInt(3, user.getWeight());
			stmt.setInt(1, user.getId());
			stmt.execute();
			
		}

		int user_id = getIdByUsername(user.getUsername());
		stmt = conn
		.prepareStatement("UPDATE fitness_info SET weight=?, height=? WHERE fitness_info.id = ?;");

		
		stmt.setInt(1, user.getWeight());
		stmt.setInt(2, user.getHeight());
		stmt.setInt(3, user_id);

		stmt.execute();
		Database.updateFriendList(user.getId(), user.getFriends());
		
		conn.close();
		return getUserByUsername(user.getUsername());


	}
	/**
	 * @param id
	 * @param data
	 * @param start
	 * @param end
	 * @throws SQLException
	 */
	public static int getData(int id, String data, Date start, Date end) throws
	// TODO Auto-generated method stub
	ClassNotFoundException, SQLException {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT \"user\".email FROM public.\"user\" WHERE id = ?;");

		stmt.setInt(1, id);

		// return the security question associated with that username's account
		try {
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int result = rs.getInt(1);

			// Checks for null query
			if (!(result == 0)) {
				conn.close();
				return result;
			}
		} catch (SQLException e) {
			System.err.print(e);
			System.out.println(": Username Invalid");

			// if there was an error return null
			conn.close();
			return 0;
		}
		// since result query was null return null
		conn.close();
		return 0;

	}

	/**
	 * Checks to see if a given username is in use.
	 * 
	 * @param user
	 * @return False is the given username is not in use, true if it is
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static boolean isUsername(String user) throws SQLException,
	ClassNotFoundException {

		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbc:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT \"user\".username FROM public.\"user\" WHERE \"user\".username = ?;");
		stmt.setString(1, user);
		try {

			// executes query
			ResultSet rs = stmt.executeQuery();
			rs.next();
			if (rs.getString(1).equals(user)){
				conn.close();
				return true;
			}

		} catch (SQLException e) {
			conn.close();
			return false;
		}
		conn.close();
		return false;
	}

	public static int getExerciseTypeId(String exerciseType) {
		return 0;
	}

	public static String getExerciseTypeById(int id) {
		return null;
	}

	public static String getPasswordByUsername(String username) throws SQLException, ClassNotFoundException {
		User user = Database.getUserByUsername(username);
		return user.getPassword();
	}

	/**
	 * Queries the database for the members of the first "seeList" of workouts.
	 * 
	 * @return An array containing the names of the primary categories. Eg:
	 *         Upper Body Lifts, etc.
	 * @author Chris and Austin
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static String[] getPrimarySeeList(boolean workout) throws SQLException,
	ClassNotFoundException {
		// Testing stub
		/*
		 * String[] test = {"Weight", "Upper Body Lifts", "Lower Body Lifts"};
		 * return test;
		 */

		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbc:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn.prepareStatement(
				"SELECT category FROM exercise_category;",
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

		ResultSet rs = stmt.executeQuery();
		int i = 0;
		// counts number of results
		while (rs.next()) {
			i++;
		}
		// goes back to top of result list
		rs.first();

		// adds results to array
		String[] result;
		int k = 0;
		if(!workout){
			i++;
			 result = new String[i];
			result[0] = "Weight";
			k++;
			
			
		}else{
			result = new String[i];
		}
		

		for (; k < i ; k++) {
			result[k] = rs.getString(1);
			rs.next();
		}
		conn.close();
		return result;

	}

	/**
	 * Queries the database for the workouts which fall under the category given
	 * by the argument.
	 * 
	 * @param primary
	 *            The category of workouts to retrieve
	 * @return An array containing the names of the workouts in the given
	 *         category
	 * @author Chris and Austin
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static String[] getSecondarySeeList(String primary)
	throws SQLException, ClassNotFoundException {
		 

		/*
		//more testing stuff
		if(primary.equals("Timed"))
		{
			String[] test2 = {"100m","440m","880m","Mile"};
			return test2;
		}
		
		if(primary.equals("Reps"))
		{
			String[] test3 = {"Pushups","Thrusters","Pullups"};
			return test3;
		}
		
		if(primary.equals("Weight"))
		{
			String[] test4 = {"BenchPress","Thrusters","Squats"};
			return test4;
		}
		
		if (primary.equalsIgnoreCase("Weight")) {
			return null;
		}
		// Testing stub
		String[] test = {"Weight", "Upper Body Lifts", "Lower Body Lifts"};
		return test;
		*/
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbc:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		// gets the id of the given category

		PreparedStatement stmt = conn
		.prepareStatement("SELECT exercise_category.id FROM public.exercise_category WHERE exercise_category.category = ?;");
		stmt.setString(1, primary);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		int catId = rs.getInt(1);

		// gets an array of all workouts in the given category
		Statement stmt2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
		rs = stmt2
		.executeQuery("SELECT exercise_types.\"name\" FROM public.exercise_types WHERE exercise_types.category = "
				+ catId + ";");

		int i = 0;
		// counts number of results
		while (rs.next()) {
			i++;
		}
		// goes back to top of result list
		rs.first();

		// adds results to array
		String[] result;
		int k = 0;
		if(catId!=0){
			i++;
			 result = new String[i];
			result[0] = "Weight";
			k++;
			
			
		}else{
			result = new String[i];
		}
		

		for (; k < i ; k++) {
			result[k] = rs.getString(1);
			rs.next();
		}
		conn.close();
		return result;

	}

	/**
	 * Gets the user's complete personal record on the given exercise.
	 * 
	 * @param The
	 *            exercise we want the PR for. This can be any value which
	 *            exists in the secondary seeList (EX: Lifts -> Deadlift)
	 * @return All of the current user's exercises of the type given in the
	 *         parameter When building the Exercise array, all data fields which
	 *         are not used should be set to -1.
	 * @author Chris (not really)
	 * @throws ParseException
	 */
	public static Exercise[] getCompletePRbyExercise(String exercise)
	throws ParseException {
		// TODO: Implement me!

		// This is a dummy return for testing purposes.
		// Constructor of Exercise is formatted:
		// Exercise(String Type, Int Weight, Int Reps, Int Time, String Date (MM/DD/YYYY))

		Exercise[] test = {
				new Exercise("Thrusters", 90, 20, -1, "01/14/2011"),
				new Exercise("Thrusters", 95, 20, -1, "01/16/2011"),
				new Exercise("Thrusters", 80, 32, 60, "01/17/2011") };
		return test;
	}

	/**
	 * Gets the user's complete personal record on the given personal stat.
	 * 
	 * @param The
	 *            statistic we want the PR for. This can be any value which
	 *            exists in the primary seeList that IS NOT a category (EX:
	 *            Weight)
	 * @return All of the current user's records on the given statistic
	 * @author Chris (not really)
	 * @throws ParseException
	 */
	public static UserStat[] getCompletePRbyStat(String stat)
	throws ParseException {
		// TODO: Implement me!

		// This is a dummy return for testing purposes.
		// Constructor of Exercise is formatted:
		// Exercise(String Type, String Date (MM/DD/YYYY), Double Value)
		UserStat[] test = { 
				new UserStat("Weight", "01/16/2010", 195.5),
				new UserStat("Weight", "01/18/2010", 195.0),
				new UserStat("Weight", "01/19/2010", 196.0) };
		return test;
	}

	/**
	 * Returns a workout object that is on "date" with id. If there is none,
	 * returns null.
	 * 
	 * @param id
	 * @param date
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Workout getWorkout(int id, Calendar date) throws SQLException, ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection("jdbs:postgresql://ieng6.ucsd.edu/"
				+ database, username, password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT user_workouts.benchmark_num,  user_workouts.prescribed, user_workouts.\"user\", user_workouts.\"time\", user_workouts.rounds, user_workouts.score " +
				"FROM public.user_workouts,  public.user_workouts WHERE id = ? AND date = ?;",
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		java.util.Date newDate = date.getTime();

		Date sqlDate = new Date(newDate.getTime());

		stmt.setInt(1, id);
		stmt.setDate(2, sqlDate);

		ResultSet rs = stmt.executeQuery();
		rs.next();
		int workoutId = rs.getInt("id");
		int benchmarkNum = rs.getInt("benchmark_num");
		boolean prescribed = rs.getBoolean("prescribed");
		int userId = rs.getInt("user");
		int workoutTime = rs.getInt("time");
		int workoutRounds = rs.getInt("rounds");
		String workoutType = rs.getString("workout_type");
		Integer score = rs.getInt("score");

		stmt = conn
		.prepareStatement("SELECT exercise.\"type\", exercise.\"time\", exercise.weight, exercise.reps FROM public.exercise WHERE exercise.id = ?;");
		stmt.setInt(1, workoutId);

		rs = stmt.executeQuery();
		rs.next();
		int numExercises = 0;
		while(rs.next()){
			numExercises++;
		}
		rs.first();
		ArrayList<Exercise> exercises = new ArrayList<Exercise>();
		for( int i = 0; i < numExercises; i++){
			int type = rs.getInt("type");
			String exercise_name = getExerciseName(type);
			int	weight = rs.getInt("weight");
			int time = rs.getInt("time");
			int reps = rs.getInt("reps");

			Exercise exercise = new Exercise(exercise_name, weight, reps, time, date);
			exercises.add(exercise);
			

		}
		
		if(workoutType.equals("Tabata")){
			Workout workout = new Tabata(id, exercises, prescribed, date, score);
		}else{
			if(workoutType.equals("AMRAP")){
				Workout workout = new AMRAP(id, workoutTime, workoutRounds, exercises, prescribed, date );

			}else{
				Workout workout = new Timed(id, workoutTime, exercises, prescribed, date);
			}
		}



		conn.close();

		// since result query was null return null
		return null;
	}

	public static String getExerciseName(int id) throws SQLException,
	ClassNotFoundException {

		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
		.prepareStatement("SELECT exercise_types.\"name\" FROM public.exercise_types WHERE exercise_types.id = ?;");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		String name = rs.getString(1);
		conn.close();
		return name;
	}

	/**
	 * getPR takes an ID and type (AMRAP, Tabata, or Timed) and returns a
	 * workout object of that type or null if it doesn't exist
	 * 
	 * @param id
	 * @param type
	 * @return
	 */
	public static Workout getPR(int id, String type, Exercise[] exerciseName) {
		// TODO Auto-generated method stub
		// returns an int for the personal record of type

		return null;
	}

	/**
	 * getPRExercise(String exerciseType, String PRType) should return an int for
	 * the personal record of exerciseType. (PR for a specific exercise)
	 * 
	 * Exercise Type "Time" (in seconds), "Reps", "Weight" (in pounds) are the only categories.
	 * 
	 * @param type
	 * @param pRType
	 * @return
	 */
	public static int getPRExercise(int id, String pRType, String exerciseType) {
		return 0;
	}

	public static boolean addGoal(int id, String goal) throws SQLException, ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
				.prepareStatement("INSERT INTO goals(goal_value, id) VALUES (?, ?);");
		stmt.setInt(2, (Integer)id);
		stmt.setString(1, goal);
		
		boolean addGoal = stmt.execute();
		conn.close();
		return true;
	}

	public static boolean removeGoal(int id, String goal) throws SQLException, ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);

		PreparedStatement stmt = conn
				.prepareStatement("DELETE FROM goals WHERE goals.id = ? AND goals.goal_value = ?;"); 
		
		stmt.setInt(1, id);
		stmt.setString(2, goal);
		
		boolean removeGoal = stmt.execute();
		conn.close();
		return removeGoal;
		

	}
	
	public static ArrayList<String> getGoals(int id) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);
		PreparedStatement stmt = conn.prepareStatement("SELECT goals.goal_value FROM public.goals WHERE goals.id = ?;");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		ArrayList<String> goals = new ArrayList<String>();
		while(rs.next()){
			try{
			goals.add(rs.getString(1));
			}catch(SQLException e){
				conn.close();
				return null;
			}
		}
		conn.close();
		return goals;
	}
	
	//return either a User object or the userID. tell samson afterwards.
	//code written already requires this to return a User object.
	public static User getUserByFriendCode(int friendCode) throws SQLException, ClassNotFoundException
	{
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);
		PreparedStatement stmt = conn.prepareStatement("SELECT \"user\".id FROM public.\"user\" WHERE \"user\".friend_code = ?;");
		stmt.setInt(1, friendCode);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		int id = rs.getInt(1);
		
		conn.close();
		return Database.getUserByID(id);
	}
	
	public static boolean addWorkout(Tabata workout) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);
		
		PreparedStatement stmt = conn.prepareStatement("INSERT INTO user_workouts(prescribed, date, \"user\", workout_type, score) VALUES (?, ?, ?, ?, ?);");
		stmt.setBoolean(1, workout.isPrescribed());
		java.util.Calendar date = workout.getCal();
		java.sql.Date sqlDate = new java.sql.Date(date.getTimeInMillis()); 
		stmt.setDate(2, sqlDate);
		stmt.setInt(3, workout.getUserID());
		stmt.setString(4, workout.getType());
		stmt.setInt(5, workout.getScore());
		try{
			stmt.execute();
		}catch(SQLException e){
			System.err.println(e);
		}
		
		ArrayList<Exercise> exercises = workout.getExercises();
		
		for(int i = 0; i < exercises.size(); i++){
			//get the id of the exercise type
			Exercise exercise = exercises.get(i);
			stmt = conn.prepareStatement("SELECT exercise_types.id FROM public.exercise_types WHERE exercise_types.\"name\" = ?;");
			stmt.setString(1, exercise.getType());
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int typeId = rs.getInt(1);
			
			//getworkoutID
			stmt = conn.prepareStatement("SELECT user_workouts.id FROM public.user_workouts WHERE user_workouts.date = ? AND user_workouts.\"user\" = ?;");
			stmt.setDate(1, sqlDate);
			stmt.setInt(2, workout.getUserID());
			rs = stmt.executeQuery();
			rs.next();
			int workoutId = rs.getInt(1);
						
			stmt = conn.prepareStatement("INSERT INTO exercise(id, \"type\", weight, reps, \"time\", date) VALUES (?, ?, ?, ?, ?, ?);");
			stmt.setInt(1, workoutId);
			stmt.setInt(2, typeId);
			
			if(exercise.getWeights() != 0)
				stmt.setInt(3, exercise.getWeights());
			else
				stmt.setNull(3, java.sql.Types.NULL);
			
			if(exercise.getReps() != 0)
				stmt.setInt(4, exercise.getReps());
			else
				stmt.setNull(4, java.sql.Types.NULL);

			if(exercise.getTime() != 0)
				stmt.setInt(5, exercise.getTime());
			else
				stmt.setNull(5, java.sql.Types.NULL);

			stmt.setDate(6, sqlDate);
			try{
				stmt.execute();
			}catch(SQLException e){
				System.err.println(e);
			}
			
		}
		conn.close();
		
		return true;
	}
	
	public static boolean addWorkout(AMRAP workout) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);
		
		PreparedStatement stmt = conn.prepareStatement("INSERT INTO user_workouts(prescribed, date, \"user\", workout_type, score) VALUES (?, ?, ?, ?, ?);");
		stmt.setBoolean(1, workout.isPrescribed());
		java.util.Calendar date = workout.getCal();
		java.sql.Date sqlDate = new java.sql.Date(date.getTimeInMillis()); 
		stmt.setDate(2, sqlDate);
		stmt.setInt(3, workout.getUserID());
		stmt.setString(4, workout.getType());
		stmt.setNull(5, java.sql.Types.NULL);
		try{
			stmt.execute();
		}catch(SQLException e){
			System.err.println(e);
		}
		
		ArrayList<Exercise> exercises = workout.getExercises();
		
		for(int i = 0; i < exercises.size(); i++){
			//get the id of the exercise type
			Exercise exercise = exercises.get(i);
			stmt = conn.prepareStatement("SELECT exercise_types.id FROM public.exercise_types WHERE exercise_types.\"name\" = ?;");
			stmt.setString(1, exercise.getType());
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int typeId = rs.getInt(1);
			
			//getworkoutID
			stmt = conn.prepareStatement("SELECT user_workouts.id FROM public.user_workouts WHERE user_workouts.date = ? AND user_workouts.\"user\" = ?;");
			stmt.setDate(1, sqlDate);
			stmt.setInt(2, workout.getUserID());
			rs = stmt.executeQuery();
			rs.next();
			int workoutId = rs.getInt(1);
						
			stmt = conn.prepareStatement("INSERT INTO exercise(id, \"type\", weight, reps, \"time\", date) VALUES (?, ?, ?, ?, ?, ?);");
			stmt.setInt(1, workoutId);
			stmt.setInt(2, typeId);
			
			if(exercise.getWeights() != 0)
				stmt.setInt(3, exercise.getWeights());
			else
				stmt.setNull(3, java.sql.Types.NULL);
			
			if(exercise.getReps() != 0)
				stmt.setInt(4, exercise.getReps());
			else
				stmt.setNull(4, java.sql.Types.NULL);

			if(exercise.getTime() != 0)
				stmt.setInt(5, exercise.getTime());
			else
				stmt.setNull(5, java.sql.Types.NULL);

			stmt.setDate(6, sqlDate);
			try{
				stmt.execute();
			}catch(SQLException e){
				System.err.println(e);
			}
			
		}
		conn.close();
		
		return true;
	}
	
	
	
	public static boolean addWorkout(Timed workout) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);
		
		PreparedStatement stmt = conn.prepareStatement("INSERT INTO user_workouts(prescribed, date, \"user\", workout_type, time) VALUES (?, ?, ?, ?, ?);");
		stmt.setBoolean(1, workout.isPrescribed());
		java.util.Calendar date = workout.getCal();
		java.sql.Date sqlDate = new java.sql.Date(date.getTimeInMillis()); 
		stmt.setDate(2, sqlDate);
		stmt.setInt(3, workout.getUserID());
		stmt.setString(4, workout.getType());
		stmt.setInt(5, workout.getTime());
		try{
			stmt.execute();
		}catch(SQLException e){
			System.err.println(e);
		}
		
		ArrayList<Exercise> exercises = workout.getExercises();
		
		for(int i = 0; i < exercises.size(); i++){
			//get the id of the exercise type
			Exercise exercise = exercises.get(i);
			stmt = conn.prepareStatement("SELECT exercise_types.id FROM public.exercise_types WHERE exercise_types.\"name\" = ?;");
			stmt.setString(1, exercise.getType());
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int typeId = rs.getInt(1);
			
			//getworkoutID
			stmt = conn.prepareStatement("SELECT user_workouts.id FROM public.user_workouts WHERE user_workouts.date = ? AND user_workouts.\"user\" = ?;");
			stmt.setDate(1, sqlDate);
			stmt.setInt(2, workout.getUserID());
			rs = stmt.executeQuery();
			rs.next();
			int workoutId = rs.getInt(1);
						
			stmt = conn.prepareStatement("INSERT INTO exercise(id, \"type\", weight, reps, \"time\", date) VALUES (?, ?, ?, ?, ?, ?);");
			stmt.setInt(1, workoutId);
			stmt.setInt(2, typeId);
			
			if(exercise.getWeights() != 0)
				stmt.setInt(3, exercise.getWeights());
			else
				stmt.setNull(3, java.sql.Types.NULL);
			
			if(exercise.getReps() != 0)
				stmt.setInt(4, exercise.getReps());
			else
				stmt.setNull(4, java.sql.Types.NULL);

			if(exercise.getTime() != 0)
				stmt.setInt(5, exercise.getTime());
			else
				stmt.setNull(5, java.sql.Types.NULL);

			stmt.setDate(6, sqlDate);
			try{
				stmt.execute();
			}catch(SQLException e){
				System.err.println(e);
			}
			
		}
		conn.close();
		return true;
	}
	
	
	
	public static Weight getWeights(int id) throws SQLException, ClassNotFoundException{
		ArrayList<Integer>weights = new ArrayList<Integer>();
		ArrayList<Calendar>dates = new ArrayList<Calendar>();
		
		Class.forName("org.postgresql.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbs:postgresql://ieng6.ucsd.edu/" + database, username,
				password);
		
		PreparedStatement stmt = conn.prepareStatement("SELECT weight.weight, weight.date FROM public.weight WHERE weight.id = ?;");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			weights.add(rs.getInt(1));
			java.sql.Date date = rs.getDate(2);
			Calendar cal = CalendarDisplay.SQLtoDate(date.toString());
			dates.add(cal);
		}
		Weight weight = new Weight(weights, dates);
		conn.close();
		return weight;
		
	}
}
